Computer applications have penetrated into all fields for a long time. Using computer networks for processing e-commerce and statistical applications has become the trend of the modern business. One example of statistical applications is to use a statistical table, which is a summary table based on the statistics of a base table. In a high-availability OLTP environment, the base table usually has a tremendous amount of data and is changing continuously. A record in the statistical table is updated according to certain rules to fulfill the access needs of the application. The statistical table allows prompt and convenient access to real-time information and can perform real-time monitoring. But such benefits require more stringent criteria for the statistical table applications.
In an OLTP environment, a system base table is an underlying table that actually stores metadata for a specific database. A user normally computes a real-time statistics based on the underlying table that has a relatively large number of records according to certain rules. In the beginning, this type of statistical strategy was able to satisfy the needs of the applications, and provide acceptable functionalities. However, as the number of users increases, the number of visits on a website increases exponentially and as a result the frequency of executing a statistical SQL (Structural Query Language) statement rises rapidly. Moreover, since the average number of records being scanned in each statistical computation increases, the average cost for each execution of an SQL statement performing a statistical function continues to rise. One example is the statistics of member reviews in a large e-commerce website. For users of a relatively high-star rating (e.g., high review rating by the other members), this kind of statistics may become extraordinarily complicated. If the number of reviews of a user is high, a database performance issue may arise when a large number of users are viewing the review results of the user displayed on a webpage at the same time, or when the web page displaying the review results is maliciously refreshed. As a result, the web page requested by the users may not be displayed even after a long period of time, resulting in poor user experience. Another example is monitoring the number of abnormal logon behaviors of a user. Under malicious logon attacks, the number of records in a base table that records logon operations of the user may reach hundreds of thousands in a short period of time. The statistical SQL that determines in real time whether a logon of the user is normal will be executed slower and slower as the number of logon attacks increases. A queue of a database server becomes longer, and the queue for connection pool also increases because an application server does not receive returned result from a database promptly. This results in a crash in the application server.
One of the prominent characteristics of OLTP in existing technologies is its frequent execution of SQL statements. Some of these SQL statements are called for completing statistical functions and are characterized by a high CPU (Central Processing Unit) occupation and a particularly high number of logical and physical reads. If the execution of these statements has reached to a certain frequency, functionalities of the database system may deteriorate, and responses of the application system to user requests become slower.
In an OLTP environment, high number of page views may be unavoidable. As described above, since computing statistics directly on the base table consumes a large amount of CPU time and has a relatively high number of logical and physical reads, the workload of the database server may always stay high. Moreover, one quite severe disadvantage of this method is that the system may have weak resistance to attacks. For a large e-commerce website, for example, if a user frequently refreshes statistics web page or continues to maliciously logon, the performance of database system may be affected, and the reliability as well as the consistency of an entire business system may be reduced.
Therefore, the traditional methods that compute statistics directly on the base table are starting to fail to meet the needs of business development, and may need to be changed urgently.